﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class dal
    {
        /// <summary>
        /// 用户登录
        /// </summary>
        /// <param name="Users_Account"></param>
        /// <param name="Users_Pwd"></param>
        /// <returns></returns>
        public SysUser Login(string Users_Account)
        {
            return DBHelper.ExecuteObject<SysUser>("select * from SysUser where Users_Account='" + Users_Account + "'");
        }

        /// <summary>
        /// 动态绑定左侧导航栏 -- 根节点
        /// </summary>
        /// <param name="RoleId"></param>
        /// <returns></returns>
        public List<SysAuthorityAndRoles> LeftBindingParent(int RoleId)
        {
            return DBHelper.ExecuteObjects<SysAuthorityAndRoles>("select * from SysAuthorityAndRoles where Roles_Id=" + RoleId);
        }
        /// <summary>
        ///  动态绑定左侧导航栏 -- 子节点
        /// </summary>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public List<SysAuthority> LeftBindingSon(int parentId)
        {
            return DBHelper.ExecuteObjects<SysAuthority>("select * from SysAuthority where ParentId=" + parentId);
        }

        /// <summary>
        ///  项目立项 -- 我的立项/列表页
        /// </summary>
        /// <returns></returns>
        public List<EABasic_Info> CapitalList(int userId)
        {
            if (userId == 1)
            {
                return DBHelper.ExecuteObjects<EABasic_Info>(@"select a.ProjectNumber,a.basic_Quota,c.Department_Name,b.Loan_Name,b.Loan_TEL,a.Basic_CreateTime,a.Basic_State
from EABasic_Info a
join EALoan_Info b on a.ProjectNumber=b.ProjectNumber
join SysDepartment c on c.Department_Id=a.The_Department");
            }
            else
            {
                return DBHelper.ExecuteObjects<EABasic_Info>(@"select a.ProjectNumber,c.Department_Name,b.Loan_Name,b.Loan_TEL,a.Basic_CreateTime,a.Basic_State
from EABasic_Info a
join EALoan_Info b on a.ProjectNumber=b.ProjectNumber
join SysDepartment c on c.Department_Id=a.The_Department
where a.Basic_CreatePeople=" + userId + "");
            }
        }
        /// <summary>
        /// 项目立项--  的立项查询/根据所属部门ID
        /// </summary>
        /// <param name="DepartmentID"></param>
        /// <returns></returns>
        public List<EABasic_Info> GetListByDepartment(int? DepartmentID)
        {
            return DBHelper.ExecuteObjects<EABasic_Info>(@"  
  select a.ProjectNumber,c.Department_Name,b.Loan_Name,b.Loan_TEL,a.Basic_CreateTime,a.Basic_State
from EABasic_Info a
join EALoan_Info b on a.ProjectNumber=b.ProjectNumber
join SysDepartment c on c.Department_Id=a.The_Department
where a.The_Department = " + DepartmentID + "");
        }
        /// <summary>
        ///  项目立项 -- 登记立项
        /// </summary>
        /// <returns></returns>
        public int CapitalAdd(EABasic_Info b)
        {
            return DBHelper.ExecuteNonQuery(@"insert into EABasic_Info values('" + b.projectNumber + "','" + b.surplus_Years + "','"
+ b.basic_Quota + "','" + b.house_Located + "','" + b.house_Purpose + "'," + b.house_Time + ",'" + b.yers_Source + "','"
+ b.assessment + "','" + b.presentation_Type + "','" + b.the_Department + "','" + b.survey + "','" + b.evaluation + "',1,'"
+ b.issue_Time + "','','','','','','','','','','getdate()','" + b.basic_CreatePeople + "')");
        }
        /// <summary>
        ///  项目立项 -- 新建借款人/可批量添加
        /// </summary>
        /// <returns></returns>
        public int Loan_Info(EALoan_Info b)
        {
            string str = @"insert into EALoan_Info values('" + b.projectNumber + "','" + b.loan_Name + "','" + b.loan_IdCard + "','"
+ b.loan_TEL + "','" + b.loan_Contacts + "','" + b.loan_ContactsTEL + "','getdate()','" + b.create_People + "')";
            return DBHelper.ExecuteNonQuery(str);
        }

        /// <summary>
        ///  项目勘察 -- 我的勘察/列表页
        /// </summary>
        /// <returns></returns>
        public List<EABasic_Info> exploreList(int userid)
        {
            if (userid == 1)
            {
                return DBHelper.ExecuteObjects<EABasic_Info>(@"select a.ProjectNumber,b.Department_Name,c.loan_Name,c.loan_TEL,a.house_Located,a.basic_CreateTime,a.Basic_State from
EABasic_Info a join SysDepartment b on a.The_Department=b.Department_Id
join EALoan_Info c on c.ProjectNumber=a.ProjectNumber
where a.Basic_State>=1 and a.Basic_State<=3");
            }
            else
            {
                return DBHelper.ExecuteObjects<EABasic_Info>(@"select a.ProjectNumber,b.Department_Name,c.loan_Name,c.loan_TEL,a.house_Located,a.basic_CreateTime,a.Basic_State from
EABasic_Info a join SysDepartment b on a.The_Department=b.Department_Id
join EALoan_Info c on c.ProjectNumber=a.ProjectNumber
where a.Survey='" + userid + "' and  a.Basic_State>=1 and a.Basic_State<=3");
            }

        }
        /// <summary>
        ///  项目勘察 -- 开始勘察
        /// </summary>
        /// <param name="projectNumber"></param>
        /// <returns></returns>
        public int startExplore(int projectNumber)
        {
            return DBHelper.ExecuteNonQuery("update EABasic_Info set Basic_State='2' where projectNumber='" + projectNumber + "'");
        }
        /// <summary>
        ///  项目勘察 -- 区位状况勘察
        /// </summary>
        /// <returns></returns>
        public int LocationAdd(EALocation l)
        {
            return DBHelper.ExecuteNonQuery(@"insert into EALoan_Info values('" + l.projectNumber + "','" + l.location_East + "','"
+ l.location_West + "','" + l.location_North + "',,'" + l.location_South + "'," + l.location_Position + ",'"
+ l.location_Traffic + "'," + l.location_Convenient + ",'" + l.location_Humanity + "'," + l.location_Quality + ","
+ l.location_Community + "," + l.location_Mature + ",'" + l.location_Facilities + "','" + l.location_Degree + "','"
+ l.location_Supporting + "','" + l.location_Explain + "','GETDATE()','" + l.location_CreatePeople + "')");
        }
        /// <summary>
        ///  项目勘察 -- 小区状况勘察
        /// </summary>
        /// <returns></returns>
        public int VillageAdd(EAVillage v)
        {
            return DBHelper.ExecuteNonQuery(@"insert into EAVillage values('" + v.projectNumber + "'," + v.village_CaseName + ","
+ v.village_Cause + "," + v.village_Close + "," + v.village_Structure + "," + v.village_Traffic + "," + v.village_Green + ","
+ v.village_Volume + "," + v.village_Parking + ",'" + v.village_Property + "','" + v.village_Explain + "','GETDATE()'),'"
+ v.village_CreatePeople + "'");
        }
        /// <summary>
        ///  项目勘察 -- 公共区域状况勘察
        /// </summary>
        /// <returns></returns>
        public int FloorsAdd(Floors f)
        {
            return DBHelper.ExecuteNonQuery(@"insert into EAFloors values('" + f.projectNumber + "','" + f.floor_Number + "','"
+ f.floor_Landmark + "','" + f.floor_Address + "'," + f.floor_HouseStructure + "," + f.floor_BuildType + "," + f.floor_Facade + ","
+ f.floor_Public + "," + f.floor_Lift + "," + f.floor_LiftNum + "," + f.floor_Gas + "," + f.floor_Water + "," + f.floor_Warm + ",'"
+ f.floor_Door + "','" + f.floor_Explain + "','GETDATE()','" + f.floor_CreatePeople + "')");
        }
        /// <summary>
        ///  项目勘察 -- 室内状况勘察
        /// </summary>
        /// <returns></returns>
        public int IndoorAdd(EAIndoor i)
        {
            return DBHelper.ExecuteNonQuery(@"insert into EAIndoor values('" + i.projectNumber + "','" + i.indoor_Purpose + "','"
+ i.indoor_LayerNum + "','" + i.indoor_Layer + "','" + i.indoor_Orientation + "','" + i.indoor_Residence + "','"
+ i.indoor_Bedroom + "','" + i.indoor_Hall + "','" + i.indoor_Toilet + "','" + i.indoor_Kitchen + "','" + i.indoor_Balcony + "','"
+ i.indoor_Terrace + "','" + i.indoor_Yard + "','" + i.indoor_HallDegree + "','" + i.living_Wall + "','" + i.living_Ceiling + "','"
+ i.living_Ground + "','" + i.indoor_RoomDegree + "','" + i.room_Wall + "','" + i.room_Ceiling + "','" + i.room_Ground + "','"
+ i.indoor_ToiletDegree + "','" + i.toilet_Wall + "','" + i.toilet_Ceiling + "','" + i.toilet_Ground + "','"
+ i.toilet_Sanitary + "','" + i.indoor_KitchenDegree + "','" + i.kitchen_Wall + "','" + i.kitchen_Ceiling + "','"
+ i.kitchen_Ground + "','" + i.kitchen_Cooking + "','" + i.indoor_Explain + "','GETDATE()','" + i.indoor_CreatePeople + "','"
+ i.livingWall_Other + "','" + i.livingCeiling_Other + "','" + i.livingGround_Other + "','" + i.roomWall_Other + "','"
+ i.roomCeiling_Other + "','" + i.roomGround_Other + "','" + i.toiletWall_Other + "','" + i.toiletCeiling_Other + "','"
+ i.toiletGround_Other + "','" + i.toiletSanitary_Other + "','" + i.kitchenWall_Other + "','" + i.kitchenCeiling_Other + "','"
+ i.kitchenGround_Other + "','" + i.kitchenCookingO_ther + "')");
        }
        /// <summary>
        ///  项目勘察 -- 现场照片提供
        /// </summary>
        /// <returns></returns>
        public int PhotoAdd(EAPhoto p)
        {
            return DBHelper.ExecuteNonQuery(@"insert into EAPhoto values('" + p.projectNumber + "','" + p.photo_Name + "','"
                + p.photo_Url + "','GETDATE()','" + p.photo_CreatePeole + "')");
        }
        /// <summary>
        ///  项目勘察 -- 添加询价结果
        /// </summary>
        /// <returns></returns>
        public int InquiryAdd(EAInquiry q)
        {
            return DBHelper.ExecuteNonQuery(@"insert into EAInquiry values('" + q.projectNumber + "','" + q.inquiry_UnitPrice + "','"
                + q.inquiry_Content + "','" + q.inquiry_Time + "','GETDATE()','" + q.inquiry_CreatePeople + "')");
        }
        /// <summary>
        ///  项目勘察 -- 询价记录
        /// </summary>
        /// <returns></returns>
        public List<EAInquiry> InquiryList(int projectNumber)
        {
            return DBHelper.ExecuteObjects<EAInquiry>("select * from EAInquiry where projectNumber='" + projectNumber + "'");
        }
        /// <summary>
        ///  项目勘察 -- 勘察详情
        /// </summary>
        /// <returns></returns>
        public EABasic_Info Details(int projectNumber)
        {
            return DBHelper.ExecuteObject<EABasic_Info>("select * from EABasic_Info where projectNumber=" + projectNumber + "");
        }
        /// <summary>
        /// 项目勘察 --  提交勘察记录
        /// </summary>
        /// <returns></returns>
        public int CommitCapital(int Basic_id)
        {
            return DBHelper.ExecuteNonQuery("update EABasic_Info set Basic_State='3' where Basic_id='" + Basic_id + "'");
        }
        /// <summary>
        ///  项目勘察 -- 项目审核记录
        /// </summary>
        /// <returns></returns>
        public List<EABasic_Info> exploreAuditingList(string stute)
        {
            return DBHelper.ExecuteObjects<EABasic_Info>(@"select * from EABasic_Info a join SysUser b on a.Basic_CreatePeople=b.Users_Id
join SysUser c on a.Survey=c.Users_Id 
join SysUser d on a.Evaluation=d.Users_Id
join EAAuditing e on e.ProjectNumber=a.ProjectNumber
where a.Basic_State in (" + stute + ")");
        }


        /// <summary>
        ///  项目评估 -- 我的评估/列表页
        /// </summary>
        /// <returns></returns>
        public List<EABasic_Info> assessList(int userid)
        {
            if (userid == 1)
            {
                return DBHelper.ExecuteObjects<EABasic_Info>(@"select a.ProjectNumber,c.Department_Name,a.survey,a.survey_StartTime,a.survey_EndTime,a.basic_CreateTime,a.basic_UnitPrice,a.basic_State
from EABasic_Info a
join EALoan_Info b on a.ProjectNumber=b.ProjectNumber
join SysDepartment c on c.Department_Id=a.The_Department
where a.basic_State>=3 and a.basic_State<=5");
            }
            else
            {
                return DBHelper.ExecuteObjects<EABasic_Info>(@"select a.ProjectNumber,c.Department_Name,a.survey,a.survey_StartTime,a.survey_EndTime,a.basic_CreateTime,a.basic_UnitPrice,a.basic_State
from EABasic_Info a
join EALoan_Info b on a.ProjectNumber=b.ProjectNumber
join SysDepartment c on c.Department_Id=a.The_Department
where a.basic_State>=3 and a.basic_State<=5 and a.Evaluation='" + userid + "'");
            }
        }
        /// <summary>
        /// 项目勘察/项目评估 -- 勘察详情
        /// </summary>
        /// <param name="projectNumber">项目表主键编号</param>
        /// <returns></returns>
        public EABasic_Info assesserList(int basic_id)  // 项目表
        {
            return DBHelper.ExecuteObject<EABasic_Info>("select * from EABasic_Info where basic_id=" + basic_id + "");
        }
        public EALocation local(int projectNumber)  // 区位状况勘察  <param name="projectNumber">项目编号</param>
        {
            return DBHelper.ExecuteObject<EALocation>("select * from EALocation where projectNumber=" + projectNumber + "");
        }
        public EAVillage village(int projectNumber)  // 小区状况
        {
            return DBHelper.ExecuteObject<EAVillage>("select * from EAVillage where projectNumber=" + projectNumber + "");
        }
        public Floors floors(int projectNumber)  // 楼状况
        {
            return DBHelper.ExecuteObject<Floors>("select * from Floors where projectNumber=" + projectNumber + "");
        }
        public EAIndoor Indoor(int projectNumber)  // 室内状况
        {
            return DBHelper.ExecuteObject<EAIndoor>("select * from EAIndoor where projectNumber=" + projectNumber + "");
        }

        /// <summary>
        ///  系统管理 -- 部门列表
        /// </summary>
        /// <returns></returns>
        public List<SysDepartment> deptList()
        {
            return DBHelper.ExecuteObjects<SysDepartment>("select * from SysDepartment");
        }
        /// <summary>
        ///  系统管理 -- 部门添加
        /// </summary>
        /// <param name="dept"></param>
        /// <returns></returns>
        public int deptAdd(SysDepartment dept)
        {
            return DBHelper.ExecuteNonQuery("insert into SysDepartment values('" + dept.department_Name + "','此角色的状态 1为启用 0为停用','1',GETDATE(),'王慧君')");
        }
        /// <summary>
        ///  系统管理 -- 角色列表
        /// </summary>
        /// <returns></returns>
        public List<SysRoles> rolesList()
        {
            return DBHelper.ExecuteObjects<SysRoles>("select * from SysRoles");
        }
        /// <summary>
        ///  系统管理 -- 角色添加
        /// </summary>
        /// <param name="role"></param>
        /// <returns></returns>
        public int rolesAdd(SysRoles role)
        {
            return DBHelper.ExecuteNonQuery("insert into SysRoles values('" + role.roles_Name + "','此角色的状态 1为启用 0为停用','1',GETDATE(),'吴斌')");
        }
        /// <summary>
        ///  系统管理 -- 用户列表
        /// </summary>
        /// <returns></returns>
        public List<SysUser> userList()
        {
            return DBHelper.ExecuteObjects<SysUser>("select * from SysUser");
        }
        /// <summary>
        ///  系统管理 -- 用户添加
        /// </summary>
        /// <param name="u"></param>
        /// <returns></returns>
        public int userAdd(SysUser u)
        {
            return DBHelper.ExecuteNonQuery("insert into SysUser values('" + u.users_Name + "','" + u.users_Account + "','123','" + u.users_TEL + "','" + u.roles_id + "','" + u.department_Id + "','1','GETDATE()','吴斌')");
        }
        /// <summary>
        ///  系统管理 -- 菜单列表
        /// </summary>
        /// <returns></returns>
        public List<SysAuthorityAndRoles> andList()
        {
            return DBHelper.ExecuteObjects<SysAuthorityAndRoles>("");
        }
        /// <summary>
        ///  系统管理 -- 字典表
        /// </summary>
        /// <returns></returns>
        public List<SysDictionary> dictionary()
        {
            return DBHelper.ExecuteObjects<SysDictionary>("");
        }

        /// <summary>
        /// 项目审核  --  审核详情列表页
        /// </summary>
        /// <returns></returns>
        public List<EAAuditing> EAAuditingXQ()
        {
            return DBHelper.ExecuteObjects<EAAuditing>(@"select e.ProjectNumber,e.Auditing_CreateTime,e.Auditing_Type,a.Basic_State,b.Users_Name from
EABasic_Info a join SysUser b on a.Basic_CreatePeople=b.Users_Id
join EAAuditing e on e.ProjectNumber=a.ProjectNumber
where a.Basic_State>=5");
        }
        /// <summary>
        ///  字典查询
        /// </summary>
        /// <param name="dicType"></param>
        /// <returns></returns>
        public List<SysDictionary> dictionary(string dicType)
        {
            return DBHelper.ExecuteObjects<SysDictionary>("select * from SysDictionary where Dictionary_Type='" + dicType + "'");
        }
    }
}
